@ECHO OFF
REM *******************************************************************************************/
REM	Setup.BAT
REM
REM	Params: 	Server
REM			MDF Path   (odd file filegroup  & primary filegroup)		 
REM			Log Filename	
REM
REM	Dependencies:
REM	The SQL Server utility osql must be located on the computer's path
REM
REM
REM       Date                   Author                  Description
REM ----------		------------------		----------------------------------------------------
REM 08/10/2009          revans              Script to install AuthZServices to existing DB
REM *******************************************************************************************/


REM -- Setting Variables
SET ProjectName=AuthZServices Database Installation
SET ExitCode=0

REM ***********************************************
REM -- These variables are not needed for this install
REM SET DBFileSize=50
REM SET DBFileGrowth=50
REM SET LogFileSize=50
REM SET LogFileGrowth=50
REM ***********************************************

REM -- Checking Parameters
REM SET CMDFile=%~0
SET CMDFile=Setup
IF "%~1" == "" (GOTO BadParam) ELSE (SET Server=%~1)
IF "%~2" == "" (GOTO BadParam) ELSE (SET Database=%~2)

REM IF "%~3" == "" (GOTO BadParam) ELSE (SET MDFPath=%~3)
REM IF "%~4" == "" (GOTO BadParam) ELSE (SET LDFPath=%~4)

SET LogFile="%CMDFile%.log"


REM -- Displaying message on screen
ECHO *******************************************************************************
ECHO Executing %ProjectName% scripts on \\%Server%\%Database%
ECHO at %DATE% %TIME%.
ECHO *******************************************************************************
ECHO Connecting to \\%Server%...

REM -- Writing message to Log File
ECHO *******************************************************************************	>>  %LogFile%
ECHO Executing %ProjectName% on \\%Server%\%Database%		>> %LogFile%
ECHO at %DATE% %TIME%.					>> %LogFile%
ECHO *******************************************************************************						>> %LogFile%
ECHO Connecting to \\%Server%...																			>> %LogFile%

REM -- Connecting to SQL Server
CALL osql -S "%Server%" -d "master" -E -n -b -Q "PRINT 'Success'"	>> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

REM ***********************************************
REM -- The database must already exist so no database gets created in this script.
REM ECHO Creating Database %Database% if it doesn't exist.
REM ECHO.																										>> %LogFile%
REM ECHO ------------------------------------------------------------------	 >> %LogFile%
REM ECHO Creating Database %Database% if it doesn't exist														>> %LogFile%
REM ECHO ------------------------------------------------------------------	 >> %LogFile%
REM REM -- Creating Database if doesn't exist...

REM SET MDF_Filename=%MDFPath%%Database%_Data.mdf
REM SET LDF_Filename=%LDFPath%%Database%_Log.ldf
REM SET SQL_Line1=IF NOT EXISTS(SELECT * FROM master.dbo.SysDatabases WHERE Name = '%Database%')
REM SET SQL_Line2=CREATE DATABASE [%Database%] ON PRIMARY
REM SET SQL_Line3=(NAME = '%Database%_Data', FILENAME = '%MDF_Filename%', SIZE = %DBFileSize%,  FILEGROWTH = %DBFileGrowth%)
REM SET SQL_Line4=LOG ON
REM SET SQL_Line5=(NAME = '%Database%_Log',  FILENAME = '%LDF_Filename%', SIZE = %LogFileSize%, FILEGROWTH = %LogFileGrowth%)

REM CALL osql -S "%Server%" -d "master" -E -n -b -Q "%SQL_Line1% %SQL_Line2% %SQL_Line3% %SQL_Line4% %SQL_Line5%"	>> %LogFile%

REM ECHO Database creation is completed for %Database% . >> %LogFile%
REM ECHO Database creation is completed for %Database% . 

REM IF %ERRORLEVEL% NEQ 0 (GOTO Crash)
REM ***********************************************


ECHO -------------------------------------------------------------------
ECHO Creating Tables and Relationships - AuthZServices					
ECHO -------------------------------------------------------------------

ECHO -------------------------------------------------------------------	>> %LogFile%
ECHO Creating Tables and Relationships - AuthZServices						>> %LogFile%
ECHO ------------------------------------------------------------------- 	>> %LogFile%

ECHO Creating AuthorizationGroup...                                                       >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Tables\AuthorizationGroup.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZUser...                                                       >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Tables\AuthZUser.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating Role...                                                       >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Tables\Role.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZUserRole...                                                       >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Tables\AuthZUserRole.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZUserRoleMapping...                                                       >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Tables\AuthZUserRoleMapping.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating RoleMapping...                                                       >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Tables\RoleMapping.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Tables and Relationships created successfully - AuthZServices 	    	>> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%

ECHO ---------------------------------------------------------------------	
ECHO Tables and Relationships created successfully - AuthZServices 
ECHO ---------------------------------------------------------------------
 
 
 

ECHO -------------------------------------------------------------------
ECHO Creating Views - AuthZServices					
ECHO -------------------------------------------------------------------

ECHO -------------------------------------------------------------------	>> %LogFile%
ECHO Creating Views - AuthZServices						                    >> %LogFile%
ECHO ------------------------------------------------------------------- 	>> %LogFile%

ECHO Creating AuthZ_vwUserRole...                                                      >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Views\AuthZ_vwUserRole.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Views created successfully - AuthZServices 	    	                >> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%

ECHO ---------------------------------------------------------------------	
ECHO Views created successfully - AuthZServices 
ECHO ---------------------------------------------------------------------
 



ECHO -------------------------------------------------------------------
ECHO Creating Stored Procedures - AuthZServices						    
ECHO -------------------------------------------------------------------

ECHO -------------------------------------------------------------------	>> %LogFile%
ECHO Creating Stored Procedures - AuthZServices						        >> %LogFile%
ECHO ------------------------------------------------------------------- 	>> %LogFile%

ECHO Creating AuthZ_spDeleteAllUsersForRole...      			                                 >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spDeleteAllUsersForRole.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetChildRoles...      			                                 >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetChildRoles.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetGroupByRoles...      							                   >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetGroupsByRoles.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetParentByRoles...                                                 >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetParentRoles.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetPeople...                                                   >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetPeople.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetRolesByGroup...                                                   >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetRolesByGroup.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetRolesByGroup...                                                             >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetRolesByPersonnelNumber.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetRolesByGroup...                                                  >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetRolesByUser.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetUsersByAlias...                                                  >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetUserByAlias.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spGetUsersByRole...                                                   >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spGetUsersByRole.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spInsertUserToRole...                                                   >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spInsertUserToRole.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spIsInRole...                                                   >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spIsInRole.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO Creating AuthZ_spUpdateAuthZUserRole...                                                   >> %LogFile%
CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\SP\AuthZ_spUpdateAuthZUserRole.sql"    >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	
ECHO Stored Procedures created successfully - AuthZServices 			
ECHO ---------------------------------------------------------------------	

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Stored Procedures created successfully - AuthZServices 		        >> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%




ECHO -------------------------------------------------------------------
ECHO Setting permissions - AuthZServices						    
ECHO -------------------------------------------------------------------

ECHO -------------------------------------------------------------------	>> %LogFile%
ECHO Setting permissions - AuthZServices						            >> %LogFile%
ECHO ------------------------------------------------------------------- 	>> %LogFile%

CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Permissions\Permission.sql" >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	
ECHO Permissions set successfully - AuthZServices 
ECHO ---------------------------------------------------------------------	

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Permissions set successfully - AuthZServices							>> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%




ECHO -------------------------------------------------------------------
ECHO Inserting Role/Permission Data - AuthZServices						    
ECHO -------------------------------------------------------------------

ECHO -------------------------------------------------------------------	>> %LogFile%
ECHO Inserting Role/Permission Data - AuthZServices						    >> %LogFile%
ECHO ------------------------------------------------------------------- 	>> %LogFile%

CALL osql -S "%Server%" -d "%Database%" -E -n -b -i "..\Data\AuthZDataLoad.sql" >> %LogFile%
IF %ERRORLEVEL% NEQ 0 (GOTO Crash)

ECHO ---------------------------------------------------------------------	
ECHO Role/Permission Data inserted successfully - AuthZServices 
ECHO ---------------------------------------------------------------------	

ECHO ---------------------------------------------------------------------	>> %LogFile%
ECHO Role/Permission Data inserted successfully - AuthZServices				>> %LogFile%
ECHO ---------------------------------------------------------------------	>> %LogFile%



REM -- Displaying message on screen
ECHO *******************************************************************************
ECHO All the %ProjectName% - Scripts executed successfully on \\%Server%\%Database%
ECHO at %DATE% %TIME%.
ECHO.
ECHO Please check the log file %LogFile% for details.
ECHO *******************************************************************************

REM -- Writing message to Log File
ECHO.																										>> %LogFile%
ECHO *******************************************************************************						>> %LogFile%
ECHO All the %ProjectName% scripts executed successfully on \\%Server%\%Database%						>> %LogFile%
ECHO at %DATE% %TIME%.																						>> %LogFile%
ECHO *******************************************************************************						>> %LogFile%

GOTO End


REM -- Subroutine: BadParam --------------------------------------------------------
:BadParam
ECHO *******************************************************************************
ECHO Please enter valid parameters to execute this script.
ECHO.
REM %CMDFile% "Server" "MDF Path " "Log Path " 
ECHO %CMDFile% "Server" "Database" 
ECHO.
REM ECHO For example: %CMDFile% "MyServer" "H:\DataFiles\"  "H:\Logfile\"
ECHO For example: %CMDFile% "MyServer" "MyDatabase"
ECHO.
REM ECHO NOTE: Make sure your MDF and LDF paths end with a back slash (\).
ECHO *******************************************************************************

SET ExitCode=1
GOTO End
REM --------------------------------------------------------------------------------


REM -- Subroutine: Crash -----------------------------------------------------------
:Crash
ECHO *******************************************************************************
ECHO Failed to execute all the  %ProjectName% scripts on \\%Server%\%Database%
ECHO at %DATE% %TIME%.
ECHO.
ECHO Please check the log file %LogFile% for Errors!
ECHO *******************************************************************************

SET ExitCode=1
GOTO End
REM --------------------------------------------------------------------------------


REM -- Subroutine: End -------------------------------------------------------------
:End

REM --------------------------------------------------------------------------------
